package com.iris.live.services.data.repositories.impl;

import com.iris.live.services.common.StringUtils;
import com.iris.live.services.data.repositories.DataCheckRepository;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by lusong on 2017/5/23.
 */
@Repository
@Transactional//(readOnly = true)
public class DataCheckRepositoryImpl extends BaseRepository implements DataCheckRepository {

    @Override
    public List getScFalseRate(String permit, String nowDate, String startDate, String endDate) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT " +
                " 	a.permit, " +
                " 	a.sc, " +
                " 	concat(format(SUM(IF(a.d_io_dt IS  NULL OR a.d_io_dt < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') 订单日期, " +
                " 	concat(format(SUM(IF(a.d_order_cus_name IS  NULL OR a.d_order_cus_name < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') 订单人姓名, " +
                " 	concat(format(SUM(IF(a.d_io_amount IS  NULL OR a.d_io_amount < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') 订金金额, " +
                " 	concat(format(SUM(IF(a.d_contract_signed_dt IS  NULL OR a.d_contract_signed_dt < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') 合同签约日期, " +
                " 	concat(format(SUM(IF(a.d_invoice_dt IS  NULL OR a.d_invoice_dt < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') 开票日期, " +
                " 	concat(format(SUM(IF(a.d_invoice_uname IS  NULL OR a.d_invoice_uname < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') 开票人姓名, " +
                " 	concat(format(SUM(IF(a.d_deliver_dt IS  NULL OR a.d_deliver_dt < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') 交车日期, " +
                " 	concat(format(SUM(IF(a.d_price_ti IS  NULL OR a.d_price_ti < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') 车辆销售价（含税）, " +
                " 	concat(format(SUM(IF(a.d_freeby_in IS  NULL OR a.d_freeby_in < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') 经销商随车_保险保费金额, " +
                " 	concat(format(SUM(IF(a.d_freeby_sub_int IS  NULL OR a.d_freeby_sub_int < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') 经销商随车_贴息金额, " +
                " 	concat(format(SUM(IF(a.d_freeby_a IS  NULL OR a.d_freeby_a < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') 经销商随车_A保次数, " +
                " 	concat(format(SUM(IF(a.d_freeby_b IS  NULL OR a.d_freeby_b < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') 经销商随车_B保次数, " +
                " 	concat(format(SUM(IF(a.d_freeby_si IS  NULL OR a.d_freeby_si < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') 经销商随车_尚银卡次数, " +
                " 	concat(format(SUM(IF(a.d_freeby_bl IS  NULL OR a.d_freeby_bl < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') 经销商随车_逸蓝卡次数, " +
                " 	concat(format(SUM(IF(a.d_freeby_sh IS  NULL OR a.d_freeby_sh < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') 经销商随车_购物卡价值, " +
                " 	concat(format(SUM(IF(a.d_freeby_oi IS  NULL OR a.d_freeby_oi < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') 经销商随车_加油卡价值, " +
                " 	concat(format(SUM(IF(a.d_freeby_bo IS  NULL OR a.d_freeby_bo < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') 经销商随车精品价值, " +
                " 	concat(format(SUM(IF(a.d_freeby_is_in IS  NULL OR a.d_freeby_is_in < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') BMBS随车_保险, " +
                " 	concat(format(SUM(IF(a.d_freeby_is_sssc IS  NULL OR a.d_freeby_is_sssc < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') BMBS随车_SSSC, " +
                " 	concat(format(SUM(IF(a.d_freeby_is_sd IS  NULL OR a.d_freeby_is_sd < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') BMBS随车_SD卡, " +
                " 	concat(format(SUM(IF(a.d_freeby_is_oil IS  NULL OR a.d_freeby_is_oil < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') BMBS随车_加油卡, " +
                " 	concat(format(SUM(IF(a.d_freeby_is_pre IS  NULL OR a.d_freeby_is_pre < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') BMBS随车_实物礼品, " +
                " 	concat(format(SUM(IF(a.d_freeby_is_ti IS  NULL OR a.d_freeby_is_ti < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') BMBS随车_服务礼券, " +
                " 	concat(format(SUM(IF(a.d_pur_tax_si IS  NULL OR a.d_pur_tax_si < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') BMBS购置税补贴SI, " +
                " 	concat(format(SUM(IF(a.d_bo_price_ti IS  NULL OR a.d_bo_price_ti < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') 精品销售额（含税）, " +
                " 	concat(format(SUM(IF(a.d_ex_in_price_ti IS  NULL OR a.d_ex_in_price_ti < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') 五星延保销售额（含税）, " +
                " 	concat(format(SUM(IF(a.d_lic_fee IS  NULL OR a.d_lic_fee < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') 上牌服务费, " +
                " 	concat(format(SUM(IF(a.d_oth_fee IS  NULL OR a.d_oth_fee < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') 其他服务费, " +
                " 	concat(format(SUM(IF(a.d_fin_fee IS  NULL OR a.d_fin_fee < 0, 1, 0)) / COUNT(a.sc) * 100,2),'%') 金融服务费 " +
                " FROM t_sales_consultant_check a " +
                " WHERE a.permit = :permit " +
                " AND a.import_batch >= DATE_FORMAT(CURDATE(),'%Y%m%01') " +
                " AND a.compare_result <> '00' " +
                " AND a.compare_result IS NOT NULL ");
        Map param = new HashMap();
        param.put("permit", permit);
        sb.append(" GROUP BY a.sc ");
        return this.getList(sb.toString(), param);
    }



    public List getFinanceFalseRate (String permit, String nowDate, String startDate, String endDate) {
        StringBuffer sb = new StringBuffer();
        Map param = new HashMap();
        sb.append(" SELECT " +
                " 	a.permit, " +
                " 	a.vin, " +
                " 	concat(format(SUM(IF(a.deduction_loan_cust_name IS  NULL OR a.deduction_loan_cust_name < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 贷款人姓名, " +
                " 	concat(format(SUM(IF(a.deduction_loan_approval_date IS  NULL OR a.deduction_loan_approval_date < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 贷款申请日期, " +
                " 	concat(format(SUM(IF(a.deduction_finance_company IS  NULL OR a.deduction_finance_company < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 金融机构, " +
                " 	concat(format(SUM(IF(a.deduction_deposit_payment IS  NULL OR a.deduction_deposit_payment < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 保证金金额, " +
                " 	concat(format(SUM(IF(a.deduction_down_payment IS  NULL OR a.deduction_down_payment < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 首付金额, " +
                " 	concat(format(SUM(IF(a.deduction_loan_payment IS  NULL OR a.deduction_loan_payment < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 贷款金额, " +
                " 	concat(format(SUM(IF(a.deduction_loan_term IS  NULL OR a.deduction_loan_term < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 贷款期数, " +
                " 	concat(format(SUM(IF(a.deduction_yearly_rate IS  NULL OR a.deduction_yearly_rate < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 利率_费率, " +
                " 	concat(format(SUM(IF(a.deduction_BMBS_discount IS  NULL OR a.deduction_BMBS_discount < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 厂家贴息金额, " +
                " 	concat(format(SUM(IF(a.deduction_dealer_discount IS  NULL OR a.deduction_dealer_discount < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 经销商贴息金额, " +
                " 	concat(format(SUM(IF(a.deduction_account_date IS  NULL OR a.deduction_account_date < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 放款到账日, " +
                " 	concat(format(SUM(IF(a.deduction_finance_fee IS  NULL OR a.deduction_finance_fee < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 金融手续费, " +
                " 	concat(format(SUM(IF(a.deduction_finance_service_fee IS  NULL OR a.deduction_finance_service_fee < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 金融服务费 " +
                " FROM t_finance_check a " +
                " WHERE a.permit = :permit " +
                " AND a.import_batch >= DATE_FORMAT(CURDATE(),'%Y%m%01') " +
                " AND a.compare_result <> '00' " +
                " AND a.compare_result IS NOT NULL ");
        param.put("permit", permit);
        sb.append(" GROUP BY a.permit ");
        return this.getList(sb.toString(), param);
    }

    public List getInsuranceFalseRate (String permit, String nowDate, String startDate, String endDate) {
        StringBuffer sb = new StringBuffer();
        Map param = new HashMap();
        sb.append(" SELECT " +
                " 	a.permit, " +
                " 	a.vin, " +
                " 	concat(format(SUM(IF(a.deduction_insured_cust_name IS  NULL OR a.deduction_insured_cust_name < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 被保险人姓名, " +
                " 	concat(format(SUM(IF(a.deduction_TCI_policy_num IS  NULL OR a.deduction_TCI_policy_num < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 交强险保单号, " +
                " 	concat(format(SUM(IF(a.deduction_VCI_policy_num IS  NULL OR a.deduction_VCI_policy_num < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 商业险保单号, " +
                " 	concat(format(SUM(IF(a.deduction_TCI_invoice_date IS  NULL OR a.deduction_TCI_invoice_date < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 交强险出单日期, " +
                " 	concat(format(SUM(IF(a.deduction_VCI_invoice_date IS  NULL OR a.deduction_VCI_invoice_date < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 商业险出单日期, " +
                " 	concat(format(SUM(IF(a.deduction_TCI_effect_date IS  NULL OR a.deduction_TCI_effect_date < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 交强险起保日期, " +
                " 	concat(format(SUM(IF(a.deduction_TCI_expiration_date IS  NULL OR a.deduction_TCI_expiration_date < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 交强险到期日, " +
                " 	concat(format(SUM(IF(a.deduction_VCI_effect_date IS  NULL OR a.deduction_VCI_effect_date < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 商业险起保日期, " +
                " 	concat(format(SUM(IF(a.deduction_VCI_expiration_date IS  NULL OR a.deduction_VCI_expiration_date < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 商业险到期日, " +
                " 	concat(format(SUM(IF(a.deduction_TCI_company IS  NULL OR a.deduction_TCI_company < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 交强险保险公司, " +
                " 	concat(format(SUM(IF(a.deduction_VCI_company IS  NULL OR a.deduction_VCI_company < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 商业险保险公司, " +
                " 	concat(format(SUM(IF(a.deduction_TCI_price IS  NULL OR a.deduction_TCI_price < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 交强险保费, " +
                " 	concat(format(SUM(IF(a.deduction_VCI_price IS  NULL OR a.deduction_VCI_price < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 商业险保费, " +
                " 	concat(format(SUM(IF(a.deduction_TCI_fee IS  NULL OR a.deduction_TCI_fee < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 交强险手续费, " +
                " 	concat(format(SUM(IF(a.deduction_VCI_fee IS  NULL OR a.deduction_VCI_fee < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 商业险手续费 " +
                " FROM t_insurance_check a " +
                " WHERE a.permit = :permit " +
                " AND a.import_batch >= DATE_FORMAT(CURDATE(),'%Y%m%01') " +
                " AND a.compare_result <> '00' " +
                " AND a.compare_result IS NOT NULL ");
        param.put("permit", permit);
        sb.append(" GROUP BY a.permit ");
        return this.getList(sb.toString(), param);
    }

    public List getScAdminFalseRate (String permit, String nowDate, String startDate, String endDate) {
        StringBuffer sb = new StringBuffer();
        Map param = new HashMap();
        sb.append(" SELECT " +
                " 	a.permit, " +
                " 	a.vin, " +
                " 	concat(format(SUM(IF(a.d_in_stock_dt IS  NULL OR a.d_in_stock_dt < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 入库日期, " +
                " 	concat(format(SUM(IF(a.d_out_stock_dt IS  NULL OR a.d_out_stock_dt < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 出库日期, " +
                " 	concat(format(SUM(IF(a.d_re_dt IS  NULL OR a.d_re_dt < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 上报厂家零售日期, " +
                " 	concat(format(SUM(IF(a.d_order_cus_name IS  NULL OR a.d_order_cus_name < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 订单人姓名, " +
                " 	concat(format(SUM(IF(a.d_price_ti IS  NULL OR a.d_price_ti < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 开票人姓名, " +
                " 	concat(format(SUM(IF(a.d_price_ti IS  NULL OR a.d_price_ti < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 车辆销售价（含税）, " +
                " 	concat(format(SUM(IF(a.d_freeby_in IS  NULL OR a.d_freeby_in < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 经销商随车_保险成本, " +
                " 	concat(format(SUM(IF(a.d_freeby_sub_int IS  NULL OR a.d_freeby_sub_int < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 经销商随车_贴息成本, " +
                " 	concat(format(SUM(IF(a.d_freeby_a IS  NULL OR a.d_freeby_a < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 经销商随车_A保成本, " +
                " 	concat(format(SUM(IF(a.d_freeby_b IS  NULL OR a.d_freeby_b < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 经销商随车_B保成本, " +
                " 	concat(format(SUM(IF(a.d_freeby_si IS  NULL OR a.d_freeby_si < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 经销商随车_尚银卡成本, " +
                " 	concat(format(SUM(IF(a.d_freeby_bl IS  NULL OR a.d_freeby_bl < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 经销商随车_逸蓝卡成本, " +
                " 	concat(format(SUM(IF(a.d_freeby_sh IS  NULL OR a.d_freeby_sh < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 经销商随车_购物卡成本, " +
                " 	concat(format(SUM(IF(a.d_freeby_oi IS  NULL OR a.d_freeby_oi < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 经销商随车_加油卡成本, " +
                " 	concat(format(SUM(IF(a.d_freeby_is_in IS  NULL OR a.d_freeby_is_in < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') BMBS随车_保险, " +
                " 	concat(format(SUM(IF(a.d_freeby_is_sssc IS  NULL OR a.d_freeby_is_sssc < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') BMBS随车_SSSC, " +
                " 	concat(format(SUM(IF(a.d_freeby_is_sd IS  NULL OR a.d_freeby_is_sd < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') BMBS随车_SD卡, " +
                " 	concat(format(SUM(IF(a.d_freeby_is_oil IS  NULL OR a.d_freeby_is_oil < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') BMBS随车_加油卡, " +
                " 	concat(format(SUM(IF(a.d_freeby_is_pre IS  NULL OR a.d_freeby_is_pre < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') BMBS随车_实物礼品, " +
                " 	concat(format(SUM(IF(a.d_freeby_is_ti IS  NULL OR a.d_freeby_is_ti < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') BMBS随车_服务礼券, " +
                " 	concat(format(SUM(IF(a.d_pur_tax_si IS  NULL OR a.d_pur_tax_si < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') BMBS购置税补贴SI, " +
                " 	concat(format(SUM(IF(a.d_re_si IS  NULL OR a.d_re_si < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 零售SI, " +
                " 	concat(format(SUM(IF(a.d_wh_si IS  NULL OR a.d_wh_si < 0, 1, 0)) / COUNT(a.permit) * 100,2),'%') 批售SI " +
                " FROM t_sales_admin_check a " +
                " WHERE a.permit = :permit " +
                " AND a.import_batch >= DATE_FORMAT(CURDATE(),'%Y%m%01') " +
                " AND a.compare_result <> '00' " +
                " AND a.compare_result IS NOT NULL ");
        param.put("permit", permit);
        sb.append(" GROUP BY a.permit ");
        return this.getList(sb.toString(), param);
    }

    @Override
    public List getScContrast(String permit, String nowDate, String startDate, String endDate) {
        StringBuffer sb = new StringBuffer();
        Map param = new HashMap();
        sb.append(" SELECT " +
                " 	a.id, " +
                " 	a.custom_tel AS customTel,    " +
                " 	a.sc,   " +
                " 	a.vin,   " +
                " 	a.intent_car AS intentCar, " +
                " 	a.sales_status AS salesStatus, " +
                " 	a.io_dt AS ioDt, " +
                " 	a.order_cus_name AS orderCusName, " +
                " 	a.io_amount AS ioAmount, " +
                " 	a.contract_signed_dt AS contractSignedDt, " +
                " 	a.invoice_dt AS invoiceDt, " +
                " 	a.invoice_uname AS invoiceUname, " +
                " 	a.deliver_dt AS deliverDt, " +
                " 	a.price_ti AS priceTi, " +
                " 	a.freeby_in AS freebyIn, " +
                " 	a.freeby_sub_int AS freebySubInt, " +
                " 	a.freeby_a AS freebyA, " +
                " 	a.freeby_b AS freebyB, " +
                " 	a.freeby_si AS freebySi, " +
                " 	a.freeby_bl AS freebyBl, " +
                " 	a.freeby_sh AS freebySh, " +
                " 	a.freeby_oi AS freebyOi, " +
                " 	a.freeby_bo AS freebyBo, " +
                " 	a.freeby_is_in AS freebyIsIn, " +
                " 	a.freeby_is_sssc AS freebyIsSssc, " +
                " 	a.freeby_is_sd AS freebyIsSd, " +
                " 	a.freeby_is_oil AS freebyIsOil, " +
                " 	a.freeby_is_pre AS freebyIsPre, " +
                " 	a.freeby_is_ti AS freebyIsTi, " +
                " 	a.pur_tax_si AS purTaxSi, " +
                " 	a.bo_price_ti AS boPriceTi, " +
                " 	a.ex_in_price_ti AS exInPriceTi, " +
                " 	a.lic_fee AS licFee, " +
                " 	a.oth_fee AS othFee, " +
                " 	a.fin_fee AS finFee, " +
                " 	a.c_io_dt AS cioDt, " +
                " 	a.c_order_cus_name AS corderCusName, " +
                " 	a.c_io_amount AS cioAmount, " +
                " 	a.c_contract_signed_dt AS ccontractSignedDt, " +
                " 	a.c_invoice_dt AS cinvoiceDt, " +
                " 	a.c_invoice_uname AS cinvoiceUname, " +
                " 	a.c_deliver_dt AS cdeliverDt, " +
                " 	a.c_price_ti AS cpriceTi, " +
                " 	a.c_freeby_in AS cfreebyIn, " +
                " 	a.c_freeby_sub_int AS cfreebySubInt, " +
                " 	a.c_freeby_a AS cfreebyA, " +
                " 	a.c_freeby_b AS cfreebyB, " +
                " 	a.c_freeby_si AS cfreebySi, " +
                " 	a.c_freeby_bl AS cfreebyBl, " +
                " 	a.c_freeby_sh AS cfreebySh, " +
                " 	a.c_freeby_oi AS cfreebyOi, " +
                " 	a.c_freeby_bo AS cfreebyBo, " +
                " 	a.c_freeby_is_in AS cfreebyIsIn, " +
                " 	a.c_freeby_is_sssc AS cfreebyIsSssc, " +
                " 	a.c_freeby_is_sd AS cfreebyIsSd, " +
                " 	a.c_freeby_is_oil AS cfreebyIsOil, " +
                " 	a.c_freeby_is_pre AS cfreebyIsPre, " +
                " 	a.c_freeby_is_ti AS cfreebyIsTi, " +
                " 	a.c_pur_tax_si AS cpurTaxSi, " +
                " 	a.c_bo_price_ti AS cboPriceTi, " +
                " 	a.c_ex_in_price_ti AS cexInPriceTi, " +
                " 	a.c_lic_fee AS clicFee, " +
                " 	a.c_oth_fee AS cothFee, " +
                " 	a.c_fin_fee AS cfinFee, " +
//                " 	CASE WHEN a.d_intent_car = 0 THEN '1' ELSE '0' END AS dIntentCar, " +
//                " 	CASE WHEN a.d_sales_status = 0 THEN '1' ELSE '0' END AS dSalesStatus, " +
                " 	CASE WHEN a.d_io_dt = 0 THEN '1' ELSE '0' END AS dIoDt, " +
                " 	CASE WHEN a.d_order_cus_name = 0 THEN '1' ELSE '0' END AS dOrderCusName, " +
                " 	CASE WHEN a.d_io_amount = 0 THEN '1' ELSE '0' END AS dIoAmount, " +
                " 	CASE WHEN a.d_contract_signed_dt = 0 THEN '1' ELSE '0' END AS dContractSignedDt, " +
                " 	CASE WHEN a.d_invoice_dt = 0 THEN '1' ELSE '0' END AS dInvoiceDt, " +
                " 	CASE WHEN a.d_invoice_uname = 0 THEN '1' ELSE '0' END AS dInvoiceUname, " +
                " 	CASE WHEN a.d_deliver_dt = 0 THEN '1' ELSE '0' END AS dDeliverDt, " +
                " 	CASE WHEN a.d_price_ti = 0 THEN '1' ELSE '0' END AS dPriceTi, " +
                " 	CASE WHEN a.d_freeby_in = 0 THEN '1' ELSE '0' END AS dFreebyIn, " +
                " 	CASE WHEN a.d_freeby_sub_int = 0 THEN '1' ELSE '0' END AS dFreebySubInt, " +
                " 	CASE WHEN a.d_freeby_a = 0 THEN '1' ELSE '0' END AS dFreebyA, " +
                " 	CASE WHEN a.d_freeby_b = 0 THEN '1' ELSE '0' END AS dFreebyB, " +
                " 	CASE WHEN a.d_freeby_si = 0 THEN '1' ELSE '0' END AS dFreebySi, " +
                " 	CASE WHEN a.d_freeby_bl = 0 THEN '1' ELSE '0' END AS dFreebyBl, " +
                " 	CASE WHEN a.d_freeby_sh = 0 THEN '1' ELSE '0' END AS dFreebySh, " +
                " 	CASE WHEN a.d_freeby_oi = 0 THEN '1' ELSE '0' END AS dFreebyOi, " +
                " 	CASE WHEN a.d_freeby_bo = 0 THEN '1' ELSE '0' END AS dFreebyBo, " +
                " 	CASE WHEN a.d_freeby_is_in = 0 THEN '1' ELSE '0' END AS dFreebyIsIn, " +
                " 	CASE WHEN a.d_freeby_is_sssc = 0 THEN '1' ELSE '0' END AS dFreebyIsSssc, " +
                " 	CASE WHEN a.d_freeby_is_sd = 0 THEN '1' ELSE '0' END AS dFreebyIsSd, " +
                " 	CASE WHEN a.d_freeby_is_oil = 0 THEN '1' ELSE '0' END AS dFreebyIsOil, " +
                " 	CASE WHEN a.d_freeby_is_pre = 0 THEN '1' ELSE '0' END AS dFreebyIsPre, " +
                " 	CASE WHEN a.d_freeby_is_ti = 0 THEN '1' ELSE '0' END AS dFreebyIsTi, " +
                " 	CASE WHEN a.d_pur_tax_si = 0 THEN '1' ELSE '0' END AS dPurTaxSi, " +
                " 	CASE WHEN a.d_bo_price_ti = 0 THEN '1' ELSE '0' END AS dBoPriceTi, " +
                " 	CASE WHEN a.d_ex_in_price_ti = 0 THEN '1' ELSE '0' END AS dExInPriceTi, " +
                " 	CASE WHEN a.d_lic_fee = 0 THEN '1' ELSE '0' END AS dLicFee, " +
                " 	CASE WHEN a.d_oth_fee = 0 THEN '1' ELSE '0' END AS dOthFee, " +
                " 	CASE WHEN a.d_fin_fee = 0 THEN '1' ELSE '0' END AS dFinFee, " +
                " 	a.compare_result AS compareResult, " +
                " 	a.total_score AS totalScore " +
                " FROM t_sales_consultant_check a " +
                " WHERE a.permit = :permit " +
                " AND a.compare_result <> '04' " +
                " AND a.compare_result <> '00' ");
        param.put("permit", permit);
        if (!StringUtils.isNullOrBlank(nowDate)) {
            sb.append(" AND a.import_batch = :nowDate ");
            param.put("nowDate", nowDate);
        }
        if (!StringUtils.isNullOrBlank(startDate)) {
            sb.append(" AND a.import_batch >= :startDate ");
            param.put("startDate", startDate);
        }
        if (!StringUtils.isNullOrBlank(endDate)) {
            sb.append(" AND a.import_batch <= :endDate ");
            param.put("endDate", endDate);
        }
        return this.getList(sb.toString(), param);
    }

    @Override
    public List getFinanceContrast(String permit, String nowDate, String startDate, String endDate) {
        StringBuffer sb = new StringBuffer();
        Map param = new HashMap();
        sb.append(" SELECT " +
                " 	a.id, " +
                " 	a.vin, " +
                " 	a.import_loan_cust_name AS importLoanCustName, " +
                " 	a.import_loan_approval_date AS importLoanApprovalDate, " +
                " 	a.import_finance_company AS importFinanceCompany, " +
                " 	a.import_deposit_payment AS importDepositPayment, " +
                " 	a.import_down_payment AS importDownPayment, " +
                " 	a.import_loan_payment AS importLoanPayment, " +
                " 	a.import_loan_term AS importLoanTerm, " +
                " 	round(a.import_yearly_rate, 2) AS importYearlyRate, " +
                " 	a.import_BMBS_discount AS importBmbsDiscount, " +
                " 	a.import_dealer_discount AS importDealerDiscount, " +
                " 	a.import_account_date AS importAccountDate, " +
                " 	a.import_finance_fee AS importFinanceFee, " +
                " 	a.import_finance_service_fee AS importFinanceServiceFee, " +
                " 	a.db_loan_cust_name AS dbLoanCustName, " +
                " 	a.db_loan_approval_date AS dbLoanApprovalDate, " +
                " 	a.db_finance_company AS dbFinanceCompany, " +
                " 	a.db_deposit_payment AS dbDepositPayment, " +
                " 	a.db_down_payment AS dbDownPayment, " +
                " 	a.db_loan_payment AS dbLoanPayment, " +
                " 	a.db_loan_term AS dbLoanTerm, " +
                " 	round(a.db_yearly_rate, 2) AS dbYearlyRate, " +
                " 	a.db_BMBS_discount AS dbBmbsDiscount, " +
                " 	a.db_dealer_discount AS dbDealerDiscount, " +
                " 	a.db_account_date AS dbAccountDate, " +
                " 	a.db_finance_fee AS dbFinanceFee, " +
                " 	a.db_finance_service_fee AS dbFinanceServiceFee, " +
                " 	CASE WHEN a.deduction_loan_cust_name = 0 THEN '1' ELSE '0' END AS deductionLoanCustName, " +
                " 	CASE WHEN a.deduction_loan_approval_date = 0 THEN '1' ELSE '0' END AS deductionLoanApprovalDate, " +
                " 	CASE WHEN a.deduction_finance_company = 0 THEN '1' ELSE '0' END AS deductionFinanceCompany, " +
                " 	CASE WHEN a.deduction_deposit_payment = 0 THEN '1' ELSE '0' END AS deductionDepositPayment, " +
                " 	CASE WHEN a.deduction_down_payment = 0 THEN '1' ELSE '0' END AS deductionDownPayment, " +
                " 	CASE WHEN a.deduction_loan_payment = 0 THEN '1' ELSE '0' END AS deductionLoanPayment, " +
                " 	CASE WHEN a.deduction_loan_term = 0 THEN '1' ELSE '0' END AS deductionLoanTerm, " +
                " 	CASE WHEN a.deduction_yearly_rate = 0 THEN '1' ELSE '0' END AS deductionYearlyRate, " +
                " 	CASE WHEN a.deduction_BMBS_discount = 0 THEN '1' ELSE '0' END AS deductionBmbsDiscount, " +
                " 	CASE WHEN a.deduction_dealer_discount = 0 THEN '1' ELSE '0' END AS deductionDealerDiscount, " +
                " 	CASE WHEN a.deduction_account_date = 0 THEN '1' ELSE '0' END AS deductionAccountDate, " +
                " 	CASE WHEN a.deduction_finance_fee = 0 THEN '1' ELSE '0' END AS deductionFinanceFee, " +
                " 	CASE WHEN a.deduction_finance_service_fee = 0 THEN '1' ELSE '0' END AS deductionFinanceServiceFee, " +
                " 	a.compare_result AS compareResult, " +
                " 	a.total_score AS totalScore " +
                " FROM t_finance_check a  " +
                " WHERE a.permit = :permit " +
                " AND a.compare_result <> '04' " +
                " AND a.compare_result <> '00' ");
        param.put("permit", permit);
        if (!StringUtils.isNullOrBlank(nowDate)) {
            sb.append(" AND a.import_batch = :nowDate ");
            param.put("nowDate", nowDate);
        }
        if (!StringUtils.isNullOrBlank(startDate)) {
            sb.append(" AND a.import_batch >= :startDate ");
            param.put("startDate", startDate);
        }
        if (!StringUtils.isNullOrBlank(endDate)) {
            sb.append(" AND a.import_batch <= :endDate ");
            param.put("endDate", endDate);
        }
        return this.getList(sb.toString(), param);
    }

    @Override
    public List getInsuranceContrast(String permit, String nowDate, String startDate, String endDate) {
        StringBuffer sb = new StringBuffer();
        Map param = new HashMap();
        sb.append(" SELECT " +
                " 	a.id, " +
                " 	a.vin, " +
                " 	a.import_insured_cust_name AS importInsuredCustName, " +
                " 	a.import_TCI_policy_num AS importTciPolicyNum, " +
                " 	a.import_VCI_policy_num AS importVciPolicyNum, " +
                " 	a.import_TCI_invoice_date AS importTciInvoiceDate, " +
                " 	a.import_VCI_invoice_date AS importVciInvoiceDate, " +
                " 	a.import_TCI_effect_date AS importTciEffectDate, " +
                " 	a.import_TCI_expiration_date AS importTciExpirationDate, " +
                " 	a.import_VCI_effect_date AS importVciEffectDate, " +
                " 	a.import_VCI_expiration_date AS importVciExpirationDate, " +
                " 	a.import_TCI_company AS importTciCompany, " +
                " 	a.import_VCI_company AS importVciCompany, " +
                " 	a.import_TCI_price AS importTciPrice, " +
                " 	a.import_VCI_price AS importVciPrice, " +
                " 	a.import_TCI_fee AS importTciFee, " +
                " 	a.import_VCI_fee AS importVciFee, " +
                " 	a.db_insured_cust_name AS dbInsuredCustName, " +
                " 	a.db_TCI_policy_num AS dbTciPolicyNum, " +
                " 	a.db_VCI_policy_num AS dbVciPolicyNum, " +
                " 	a.db_TCI_invoice_date AS dbTciInvoiceDate, " +
                " 	a.db_VCI_invoice_date AS dbVciInvoiceDate, " +
                " 	a.db_TCI_effect_date AS dbTciEffectDate, " +
                " 	a.db_TCI_expiration_date AS dbTciExpirationDate, " +
                " 	a.db_VCI_effect_date AS dbVciEffectDate, " +
                " 	a.db_VCI_expiration_date AS dbVciExpirationDate, " +
                " 	a.db_TCI_company AS dbTciCompany, " +
                " 	a.db_VCI_company AS dbVciCompany, " +
                " 	a.db_TCI_price AS dbTciPrice, " +
                " 	a.db_VCI_price AS dbVciPrice, " +
                " 	a.db_TCI_fee AS dbTciFee, " +
                " 	a.db_VCI_fee AS dbVciFee, " +
                " 	CASE WHEN a.deduction_insured_cust_name = 0 THEN '1' ELSE '0' END AS deductionInsuredCustName, " +
                " 	CASE WHEN a.deduction_TCI_policy_num = 0 THEN '1' ELSE '0' END AS deductionTciPolicyNum, " +
                " 	CASE WHEN a.deduction_VCI_policy_num = 0 THEN '1' ELSE '0' END AS deductionVciPolicyNum, " +
                " 	CASE WHEN a.deduction_TCI_invoice_date = 0 THEN '1' ELSE '0' END AS deductionTciInvoiceDate, " +
                " 	CASE WHEN a.deduction_VCI_invoice_date = 0 THEN '1' ELSE '0' END AS deductionVciInvoiceDate, " +
                " 	CASE WHEN a.deduction_TCI_effect_date = 0 THEN '1' ELSE '0' END AS deductionTciEffectDate, " +
                " 	CASE WHEN a.deduction_TCI_expiration_date = 0 THEN '1' ELSE '0' END AS deductionTciExpirationDate, " +
                " 	CASE WHEN a.deduction_VCI_effect_date = 0 THEN '1' ELSE '0' END AS deductionVciEffectDate, " +
                " 	CASE WHEN a.deduction_VCI_expiration_date = 0 THEN '1' ELSE '0' END AS deductionVciExpirationDate, " +
                " 	CASE WHEN a.deduction_TCI_company = 0 THEN '1' ELSE '0' END AS deductionTciCompany, " +
                " 	CASE WHEN a.deduction_VCI_company = 0 THEN '1' ELSE '0' END AS deductionVciCompany, " +
                " 	CASE WHEN a.deduction_TCI_price = 0 THEN '1' ELSE '0' END AS deductionTciPrice, " +
                " 	CASE WHEN a.deduction_VCI_price = 0 THEN '1' ELSE '0' END AS deductionVciPrice, " +
                " 	CASE WHEN a.deduction_TCI_fee = 0 THEN '1' ELSE '0' END AS deductionTciFee, " +
                " 	CASE WHEN a.deduction_VCI_fee = 0 THEN '1' ELSE '0' END AS deductionVciFee, " +
                " 	a.compare_result AS compareResult, " +
                " 	a.total_score AS totalScore " +
                " FROM t_insurance_check a " +
                " WHERE a.permit = :permit " +
                " AND a.compare_result <> '04' " +
                " AND a.compare_result <> '00' ");
        param.put("permit", permit);
        if (!StringUtils.isNullOrBlank(nowDate)) {
            sb.append(" AND a.import_batch = :nowDate ");
            param.put("nowDate", nowDate);
        }
        if (!StringUtils.isNullOrBlank(startDate)) {
            sb.append(" AND a.import_batch >= :startDate ");
            param.put("startDate", startDate);
        }
        if (!StringUtils.isNullOrBlank(endDate)) {
            sb.append(" AND a.import_batch <= :endDate ");
            param.put("endDate", endDate);
        }
        return this.getList(sb.toString(), param);
    }

    @Override
    public List getScAdminContrast(String permit, String nowDate, String startDate, String endDate) {
        StringBuffer sb = new StringBuffer();
        Map param = new HashMap();
        sb.append(" SELECT " +
                " 	a.id, " +
                " 	a.vin, " +
                " 	a.in_stock_dt AS inStockDt, " +
                " 	a.out_stock_dt AS outStockDt, " +
                " 	a.re_dt AS reDt, " +
                " 	a.order_cus_name AS orderCusName, " +
                " 	a.invoice_uname AS invoiceUname, " +
                " 	a.price_ti AS priceTi, " +
                " 	a.freeby_in AS freebyIn, " +
                " 	a.freeby_sub_int AS freebySubInt, " +
                " 	a.freeby_a AS freebyA, " +
                " 	a.freeby_b AS freebyB, " +
                " 	a.freeby_si AS freebySi, " +
                " 	a.freeby_bl AS freebyBl, " +
                " 	a.freeby_sh AS freebySh, " +
                " 	a.freeby_oi AS freebyOi, " +
                " 	a.freeby_is_in AS freebyIsIn, " +
                " 	a.freeby_is_sssc AS freebyIsSssc, " +
                " 	a.freeby_is_sd AS freebyIsSd, " +
                " 	a.freeby_is_oil AS freebyIsOil, " +
                " 	a.freeby_is_pre AS freebyIsPre, " +
                " 	a.freeby_is_ti AS freebyIsTi, " +
                " 	a.pur_tax_si AS purTaxSi, " +
                " 	a.re_si AS reSi, " +
                " 	a.wh_si AS whSi, " +
                " 	a.c_in_stock_dt AS cinStockDt, " +
                " 	a.c_out_stock_dt AS coutStockDt, " +
                " 	a.c_re_dt AS creDt, " +
                " 	a.c_order_cus_name AS corderCusName, " +
                " 	a.c_invoice_uname AS cinvoiceUname, " +
                " 	a.c_price_ti AS cpriceTi, " +
                " 	a.c_freeby_in AS cfreebyIn, " +
                " 	a.c_freeby_sub_int AS cfreebySubInt, " +
                " 	a.c_freeby_a AS cfreebyA, " +
                " 	a.c_freeby_b AS cfreebyB, " +
                " 	a.c_freeby_si AS cfreebySi, " +
                " 	a.c_freeby_bl AS cfreebyBl, " +
                " 	a.c_freeby_sh AS cfreebySh, " +
                " 	a.c_freeby_oi AS cfreebyOi, " +
                " 	a.c_freeby_is_in AS cfreebyIsIn, " +
                " 	a.c_freeby_is_sssc AS cfreebyIsSssc, " +
                " 	a.c_freeby_is_sd AS cfreebyIsSd, " +
                " 	a.c_freeby_is_oil AS cfreebyIsOil, " +
                " 	a.c_freeby_is_pre AS cfreebyIsPre, " +
                " 	a.c_freeby_is_ti AS cfreebyIsTi, " +
                " 	a.c_pur_tax_si AS cpurTaxSi, " +
                " 	a.c_re_si AS creSi, " +
                " 	a.c_wh_si AS cwhSi, " +
                " 	CASE WHEN a.d_in_stock_dt = 0 THEN '1' ELSE '0' END AS dinStockDt, " +
                " 	CASE WHEN a.d_out_stock_dt = 0 THEN '1' ELSE '0' END AS doutStockDt, " +
                " 	CASE WHEN a.d_re_dt = 0 THEN '1' ELSE '0' END AS dreDt, " +
                " 	CASE WHEN a.d_order_cus_name = 0 THEN '1' ELSE '0' END AS dorderCusName, " +
                " 	CASE WHEN a.d_invoice_uname = 0 THEN '1' ELSE '0' END AS dinvoiceUname, " +
                " 	CASE WHEN a.d_price_ti = 0 THEN '1' ELSE '0' END AS dpriceTi, " +
                " 	CASE WHEN a.d_freeby_in = 0 THEN '1' ELSE '0' END AS dfreebyIn, " +
                " 	CASE WHEN a.d_freeby_sub_int = 0 THEN '1' ELSE '0' END AS dfreebySubInt, " +
                " 	CASE WHEN a.d_freeby_a = 0 THEN '1' ELSE '0' END AS dfreebyA, " +
                " 	CASE WHEN a.d_freeby_b = 0 THEN '1' ELSE '0' END AS dfreebyB, " +
                " 	CASE WHEN a.d_freeby_si = 0 THEN '1' ELSE '0' END AS dfreebySi, " +
                " 	CASE WHEN a.d_freeby_bl = 0 THEN '1' ELSE '0' END AS dfreebyBl, " +
                " 	CASE WHEN a.d_freeby_sh = 0 THEN '1' ELSE '0' END AS dfreebySh, " +
                " 	CASE WHEN a.d_freeby_oi = 0 THEN '1' ELSE '0' END AS dfreebyOi, " +
                " 	CASE WHEN a.d_freeby_is_in = 0 THEN '1' ELSE '0' END AS dfreebyIsIn, " +
                " 	CASE WHEN a.d_freeby_is_sssc = 0 THEN '1' ELSE '0' END AS dfreebyIsSssc, " +
                " 	CASE WHEN a.d_freeby_is_sd = 0 THEN '1' ELSE '0' END AS dfreebyIsSd, " +
                " 	CASE WHEN a.d_freeby_is_oil = 0 THEN '1' ELSE '0' END AS dfreebyIsOil, " +
                " 	CASE WHEN a.d_freeby_is_pre = 0 THEN '1' ELSE '0' END AS dfreebyIsPre, " +
                " 	CASE WHEN a.d_freeby_is_ti = 0 THEN '1' ELSE '0' END AS dfreebyIsTi, " +
                " 	CASE WHEN a.d_pur_tax_si = 0 THEN '1' ELSE '0' END AS dpurTaxSi, " +
                " 	CASE WHEN a.d_re_si = 0 THEN '1' ELSE '0' END AS dreSi, " +
                " 	CASE WHEN a.d_wh_si = 0 THEN '1' ELSE '0' END AS dwhSi, " +
                " 	a.compare_result AS compareResult, " +
                " 	a.total_score AS totalScore " +
                " FROM t_sales_admin_check a " +
                " WHERE a.permit = :permit " +
                " AND a.compare_result <> '04' " +
                " AND a.compare_result <> '00' ");
        param.put("permit", permit);
        if (!StringUtils.isNullOrBlank(nowDate)) {
            sb.append(" AND a.import_batch = :nowDate ");
            param.put("nowDate", nowDate);
        }
        if (!StringUtils.isNullOrBlank(startDate)) {
            sb.append(" AND a.import_batch >= :startDate ");
            param.put("startDate", startDate);
        }
        if (!StringUtils.isNullOrBlank(endDate)) {
            sb.append(" AND a.import_batch <= :endDate ");
            param.put("endDate", endDate);
        }
        return this.getList(sb.toString(), param);
    }

    @Override
    public List getScRankingList (String permit, String startDate, String endDate, int dateNum, String cta) {
        StringBuffer sb = new StringBuffer();
        Map param = new HashMap<>();
        sb.append(" SELECT  " +
                "  	a.*,  " +
                "  	cast(@rowNum \\:= @rowNum + 1 as DECIMAL) as ranking  " +
                "  FROM (  " +
                " SELECT " +
                " 	@rowNum \\:= 0, " +
                " 	c.`许可_BMBS`, " +
                " 	c.经销商名 distributor, " +
                " 	e.`人员姓名` sc, " +
                " 	round(f.score,2) score " +
                " FROM `lsh经销商表` c " +
                " JOIN `用户` e ON c.`许可_BMBS` = e.`许可` " +
                " LEFT JOIN ( " +
                " 	SELECT " +
                " 		b.permit, " +
                " 		b.sc, " +
                " 		SUM(IFNULL(b.score, 0)) / :dateNum score " +
                " 	FROM ( " +
                " 		SELECT " +
                " 		b.permit, " +
                " 		b.import_batch, " +
                " 		b.sc, " +
                " 		CASE " +
                " 			WHEN SUM(b.state) = 10 THEN 90 + SUM(b.state * b.score) / 100 " +
                " 			WHEN SUM(b.state) = 20 THEN 80 + SUM(b.state * b.score) / 100 " +
                " 			WHEN SUM(b.state) = 30 THEN 70 + SUM(b.state * b.score) / 100 " +
                " 			WHEN SUM(b.state) = 70 THEN 30 + SUM(b.state * b.score) / 100 " +
                " 			WHEN SUM(b.state) = 80 THEN 20 + SUM(b.state * b.score) / 100 " +
                " 			WHEN SUM(b.state) = 90 THEN 10 + SUM(b.state * b.score) / 100 " +
                " 			WHEN SUM(b.state) = 100 THEN SUM(b.state * b.score) / 100 " +
                " 		ELSE '' END AS score " +
                " 	FROM " +
                " 		( " +
                " 			SELECT " +
                " 				a.permit, " +
                " 				a.import_batch, " +
                " 				a.sc, " +
                " 				a.state, " +
                " 				SUM(IF (a.total_score IS NULL OR a.total_score = '', 0, a.total_score)) / COUNT(a.total_score) score " +
                " 			FROM " +
                " 				( " +
                " 					SELECT " +
                " 						a.*, " +
                " 						CASE " +
                " 							WHEN a.sales_status = '意向金' THEN 10 " +
                " 							WHEN a.sales_status = '订金' THEN 20 " +
                " 							WHEN a.sales_status IN ('合同', '开票', '交车') THEN 70 " +
                " 						ELSE '' END state " +
                " 					FROM t_sales_consultant_check a " +
                " 				) a " +
                " 			WHERE " +
                " 				a.import_batch >= :startDate " +
                " 			AND a.import_batch <= :endDate " +
                " 			and a.sales_status IS NOT NULL " +
                " 			and a.sales_status <> '' " +
                " 			GROUP BY " +
                " 				a.permit, " +
                " 				a.import_batch, " +
                " 				a.sc, " +
                " 				a.state " +
                " 		) b " +
                " 	GROUP BY " +
                " 		b.permit, " +
                " 		b.import_batch, " +
                " 		b.sc " +
                " 	) b " +
                " 	GROUP BY " +
                " 		b.permit, " +
                " 		b.sc " +
                " ) f ON ( " +
                " 	e.`许可` = f.permit " +
                " 	AND e.`人员姓名` = f.sc " +
                " ) " +
                " WHERE " +
                " 	e.角色 = '销售顾问' " +
                "   and c.经销商类型 = 'Mother Company' and c.是否测试 = '否'  ");
        //呼伦贝尔利星(NHLL10)、鄂尔多斯之星(NORL10)
        if ("0".equals(cta)) {
            sb.append(" AND c.许可_BMBS in ('NHLL10','NORL10') ");
        }
        //北三区(N3)
        else if ("1".equals(cta)) {
            sb.append(" AND c.大区_EN = 'N3' ");
        }
        //单店
        else if ("3".equals(cta)) {
            sb.append(" AND c.许可_BMBS = :permit ");
            param.put("permit", permit);
        }
        //cta范围
        else {
            sb.append(" AND c.CTA = :cta ");
            param.put("cta", cta);
        }

        sb.append(" AND (e.`删除状态` <> '已删除' OR e.`删除状态` IS NULL)  " +
                "  	AND (e.`销售顾问职位状态` <> '离职' OR e.`销售顾问职位状态` IS NULL)  " +
                "  ) a  " +
                "  ORDER BY a.score DESC ");
        param.put("startDate", startDate);
        param.put("endDate", endDate);
        param.put("dateNum", dateNum);
        return this.getList(sb.toString(), param);
    }

    @Override
    public List getFinanceRankingList(String permit, String startDate, String endDate, int dateNum) {
        StringBuffer sb = new StringBuffer();
        Map param = new HashMap<>();
        sb.append(" SELECT " +
                " 	a.*, " +
                " 	cast(@rowNum \\:= @rowNum + 1 as DECIMAL) AS ranking " +
                " FROM ( " +
                " 	SELECT " +
                " 		@rowNum \\:= 0 a,  " +
                " 		a.`经销商名` distributor, " +
                " 		IFNULL(b.score,0) score " +
                " 	FROM ( " +
                " 		SELECT  " +
                " 			c.`许可_BMBS`, " +
                " 			c.`经销商名` " +
                " 		FROM `lsh经销商表` c  " +
                " 		JOIN (	  " +
                " 			SELECT  " +
                " 				b.`大区`  " +
                " 			FROM `lsh经销商表` b  " +
                " 			WHERE b.许可_BMBS = :permit " +
                " 		) d ON c.大区 = d.大区 " +
                "       where c.经销商类型 = 'Mother Company' and c.是否测试 = '否'  " +
                " 	) a  " +
                " 	LEFT JOIN ( " +
                " 		SELECT  " +
                " 			b.permit,  " +
                " 			format(SUM(b.score) / :dateNum, 2) score  " +
                " 		FROM (  " +
                " 			SELECT  " +
                " 				a.permit,  " +
                " 				a.import_batch,  " +
                " 				SUM(IF(a.total_score IS NULL or a.total_score = '', 0, a.total_score)) / COUNT(a.total_score) score  " +
                " 			FROM t_finance_check a  " +
                " 			WHERE a.import_batch >= :startDate " +
                " 			AND a.import_batch <= :endDate " +
                " 			GROUP BY a.permit,a.import_batch  " +
                " 		) b  " +
                " 		GROUP BY b.permit  " +
                " 	) b ON a.`许可_BMBS` = b.permit " +
                " ) a " +
                " ORDER BY cast(a.score as DECIMAL) DESC ");
        param.put("permit", permit);
        param.put("startDate", startDate);
        param.put("endDate", endDate);
        param.put("dateNum", dateNum);
        return this.getList(sb.toString(), param);
    }

    @Override
    public List getInsuranceRankingList(String permit, String startDate, String endDate, int dateNum) {
        StringBuffer sb = new StringBuffer();
        Map param = new HashMap<>();
        sb.append(" SELECT " +
                " 	a.*, " +
                " 	cast(@rowNum \\:= @rowNum + 1 as DECIMAL) AS ranking " +
                " FROM ( " +
                " 	SELECT " +
                " 		@rowNum \\:= 0 a,  " +
                " 		a.`经销商名` distributor, " +
                " 		IFNULL(b.score,0) score " +
                " 	FROM ( " +
                " 		SELECT  " +
                " 			c.`许可_BMBS`, " +
                " 			c.`经销商名` " +
                " 		FROM `lsh经销商表` c  " +
                " 		JOIN (	  " +
                " 			SELECT  " +
                " 				b.`大区`  " +
                " 			FROM `lsh经销商表` b  " +
                " 			WHERE b.许可_BMBS = :permit " +
                " 		) d ON c.大区 = d.大区 " +
                "       where c.经销商类型 = 'Mother Company' and c.是否测试 = '否'  " +
                " 	) a  " +
                " 	LEFT JOIN ( " +
                " 		SELECT  " +
                " 			b.permit,  " +
                " 			format(SUM(b.score) / :dateNum, 2) score  " +
                " 		FROM (  " +
                " 			SELECT  " +
                " 				a.permit,  " +
                " 				a.import_batch,  " +
                " 				SUM(IF(a.total_score IS NULL or a.total_score = '', 0, a.total_score)) / COUNT(a.total_score) score  " +
                " 			FROM t_insurance_check a  " +
                " 			WHERE a.import_batch >= :startDate " +
                " 			AND a.import_batch <= :endDate " +
                " 			GROUP BY a.permit,a.import_batch  " +
                " 		) b  " +
                " 		GROUP BY b.permit  " +
                " 	) b ON a.`许可_BMBS` = b.permit " +
                " ) a " +
                " ORDER BY cast(a.score as DECIMAL) DESC ");
        param.put("permit", permit);
        param.put("startDate", startDate);
        param.put("endDate", endDate);
        param.put("dateNum", dateNum);
        return this.getList(sb.toString(), param);
    }

    @Override
    public List getScAdminRankingList(String permit, String startDate, String endDate, int dateNum) {
        StringBuffer sb = new StringBuffer();
        Map param = new HashMap<>();
        sb.append(" SELECT " +
                " 	a.*, " +
                " 	cast(@rowNum \\:= @rowNum + 1 as DECIMAL) AS ranking " +
                " FROM ( " +
                " 	SELECT " +
                " 		@rowNum \\:= 0 a,  " +
                " 		a.`经销商名` distributor, " +
                " 		IFNULL(b.score,0) score " +
                " 	FROM ( " +
                " 		SELECT  " +
                " 			c.`许可_BMBS`, " +
                " 			c.`经销商名` " +
                " 		FROM `lsh经销商表` c  " +
                " 		JOIN (	  " +
                " 			SELECT  " +
                " 				b.`大区`  " +
                " 			FROM `lsh经销商表` b  " +
                " 			WHERE b.许可_BMBS = :permit " +
                " 		) d ON c.大区 = d.大区 " +
                "       where c.经销商类型 = 'Mother Company' and c.是否测试 = '否' " +
                " 	) a  " +
                " 	LEFT JOIN ( " +
                " 		SELECT  " +
                " 			b.permit,  " +
                " 			format(SUM(b.score) / :dateNum, 2) score  " +
                " 		FROM (  " +
                " 			SELECT  " +
                " 				a.permit,  " +
                " 				a.import_batch,  " +
                " 				SUM(IF(a.total_score IS NULL or a.total_score = '', 0, a.total_score)) / COUNT(a.total_score) score  " +
                " 			FROM t_sales_admin_check a  " +
                " 			WHERE a.import_batch >= :startDate " +
                " 			AND a.import_batch <= :endDate " +
                " 			GROUP BY a.permit,a.import_batch  " +
                " 		) b  " +
                " 		GROUP BY b.permit  " +
                " 	) b ON a.`许可_BMBS` = b.permit " +
                " ) a " +
                " ORDER BY cast(a.score as DECIMAL) DESC ");
        param.put("permit", permit);
        param.put("startDate", startDate);
        param.put("endDate", endDate);
        param.put("dateNum", dateNum);
        return this.getList(sb.toString(), param);
    }

}
